package com.xbts.screen.mapper;

import com.xbts.screen.domain.dto.CostDto;
import com.xbts.screen.domain.dto.HistogramDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author : llk
 * @Date: 2023/12/29 15:35
 */
public interface SyntheticScreenMapper {


    @Select("SELECT SUM(DATA0060.PARTS_ORDERED*DATA0050.unit_sq) actual, CONVERT(varchar(100),data0060.ENT_DATE ,23) 接时时间,DATA0015.ABBR_NAME 工厂,DATA0015.ppc_bal_value 月接单目标面积,\n" +
            "data0010.ABBR_NAME 客户名称,data0010.CUST_CODE 客户代码,DATA0009.ABBR_NAME  xAxis into #d0060 \n" +
            "FROM DATA0060 INNER JOIN DATA0050 ON DATA0050.RKEY=DATA0060.CUST_PART_PTR\n" +
            " INNER JOIN DATA0015 ON DATA0015.RKEY=DATA0060.WHSE_PTR_SALES\n" +
            " inner join data0010 on data0010.rkey=data0060.CUSTOMER_PTR\n" +
            " INNER JOIN DATA0009 ON DATA0009.RKEY=DATA0010.SALES_REP_PTR\n" +
            " WHERE DATA0060.ENT_DATE>=#{startTime}  AND DATA0060.ENT_DATE<=#{endTime} \n" +
            " GROUP BY CONVERT(varchar(100),data0060.ENT_DATE ,23),DATA0015.ABBR_NAME,DATA0015.ppc_bal_value,data0010.ABBR_NAME ,data0010.CUST_CODE ,DATA0009.ABBR_NAME\n" +
            " select  round( actual,2) actual,接时时间, 工厂,客户名称,客户代码, xAxis from #d0060  order by  actual desc,xAxis desc\n" +
            "drop table #d0060")
    List<HistogramDto> businessRanking(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT CONVERT(VARCHAR(10),dbo.Data0060.ENT_DATE,23) xAxis,SUM(Round(DATA0060.PARTS_ORDERED*DATA0050.unit_sq,2)) actual,DATA0015.ppc_bal_value target\n" +
            " FROM data0060 (nolock)  inner join data0050 ON Data0060.CUST_PART_PTR = Data0050.RKEY\n" +
            "    inner join data0097 (nolock) on Data0097.RKEY = Data0060.PURCHASE_ORDER_PTR\n" +
            "    INNER JOIN Data0010 (nolock) ON Data0060.CUSTOMER_PTR = Data0010.RKEY\n" +
            "    left join data0025 (nolock) on data0050.rkey=data0025.ancestor_ptr and data0025.parent_ptr=0\n" +
            "\tleft join data0017 (nolock) on data0025.inventory_ptr=data0017.rkey\n" +
            "\tleft join data0002 (nolock) on data0017.auxiliary_UNIT_PTR=data0002.rkey\n" +
            "    left join data0008 (nolock) on data0025.prod_code_ptr=data0008.rkey\n" +
            "    left join data0001 (nolock) on Data0060.currency_ptr=data0001.rkey \n" +
            "    left join data0059 (nolock)  on Data0060.customer_ptr=data0059.customer_ptr and Data0060.cust_part_ptr=data0059.cust_part_ptr\n" +
            "    left join data0015 (nolock)  on data0060.whse_ptr=data0015.rkey\n" +
            "\tleft join data0015 data0015b (nolock)  on data0060.whse_ptr_sales=data0015b.rkey\n" +
            "    left join data0012 (nolock) on data0060.cust_ship_addr_ptr=data0012.rkey\n" +
            "    LEFT OUTER JOIN Data0005 AS t3(NOLOCK) ON Data0060.printed_fob_ptr = t3.RKEY \n" +
            "\tleft join data0189(nolock) on data0060.TAX_PTR=data0189.rkey   \n" +
            "\t  left join (select max(rkey) as rkeyd502,d502.source_ptr from data0502 d502(nolock) group by d502.source_ptr ) d0502 on d0502.source_ptr=data0025.rkey   left join data0502(nolock) on data0502.rkey=d0502.rkeyd502  \n" +
            "\tleft JOIN (select SALES_ORDER_PTR,max(SALES_REP_PTR) SALES_REP_PTR  from data0102 d0102(nolock) group by SALES_ORDER_PTR,SALES_REP_PTR ) data0102 ON Data0102.SALES_ORDER_PTR = Data0060.RKEY\n" +
            "\tleft JOIN Data0009(nolock) ON Data0009.RKEY = Data0102.SALES_REP_PTR\n" +
            "    left join data0005 Data0005 (nolock) ON Data0005.RKEY = Data0060.ENTERED_BY_EMPL_PTR\n" +
            "\tleft join data0005 D05 (nolock)   ON D05.RKEY=Data0060.Conf_audit_Empl_Ptr  \n" +
            "\tLEFT JOIN data0005 Data0005_1 (nolock)  ON Data0060.CONF_BY_EMPL_PTR = Data0005_1.RKEY\n" +
            "\tleft join data0005 data0005f on  data0010.chy_ptr=data0005f.rkey\n" +
            "\tleft join data0005 data0005g(nolock)  on data0060.so_offer_ptr=data0005g.rkey\n" +
            "\tWHERE  1=1 AND\n" +
            "\tdata0060.ttype<>4  and so_style<>'2' and so_style<>'3' and so_style<>'6' and data0060.STATUS<>'5'  AND DATA0060.ENT_dATE>=#{startTime} and   DATA0060.ENT_dATE<=#{endTime}\n" +
            "\tGROUP BY CONVERT(VARCHAR(10),dbo.Data0060.ENT_DATE,23),data0015.ABBR_NAME,DATA0015.ppc_bal_value")
    List<HistogramDto> businessDetailsByDateType(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT\n" +
            "\tDATA0034.yield_object target,\n" +
            "\tDATA0034.yield_object_month temp,\n" +
            "\tdata0034.dept_name  xAxis,\n" +
            "\tround( SUM ( data0048.qty_prod* isnull( data0048.gs_sq_per_unit, data0047.Parameter_value ) ), 4 ) AS actual \n" +
            "FROM\n" +
            "\tdata0048 ( nolock )\n" +
            "\tINNER JOIN data0006 ( nolock ) ON data0048.wo_ptr= data0006.rkey\n" +
            "\tINNER JOIN data0025 ( nolock ) ON data0006.bom_ptr= data0025.rkey\n" +
            "\tINNER JOIN data0050 ( nolock ) ON data0025.ancestor_ptr= data0050.rkey\n" +
            "\tLEFT JOIN data0492 ( nolock ) ON data0006.cut_no= data0492.cut_no\n" +
            "\tLEFT JOIN data0010 ( nolock ) ON data0492.customer_ptr= data0010.rkey\n" +
            "\tLEFT JOIN data0047 ( nolock ) ON data0047.source_pointer= data0006.bom_ptr \n" +
            "\tAND data0047.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0047.PARAMETER_PTR= 4\n" +
            "\tLEFT JOIN data0047 data0047b ( nolock ) ON data0047b.source_pointer= data0006.bom_ptr \n" +
            "\tAND data0047b.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0047b.PARAMETER_PTR= 7\n" +
            "\tLEFT JOIN data0034 a ( nolock ) ON data0048.work_center_ptr= a.rkey\n" +
            "\tLEFT JOIN data0038 ( nolock ) ON data0038.source_ptr= data0025.rkey \n" +
            "\tAND data0038.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0038.step_number= data0048.step_no \n" +
            "\tAND data0038.flow_no= data0048.flow_no\n" +
            "\tLEFT JOIN data0059 ( nolock ) ON data0050.rkey= data0059.cust_part_ptr \n" +
            "\tAND data0492.customer_ptr= data0059.customer_ptr\n" +
            "\tLEFT JOIN data0034 ( nolock ) ON a.dept_ptr= data0034.rkey \n" +
            "WHERE\n" +
            "\tdata0048.outtime >= #{startTime} \n" +
            "\tAND data0048.outtime <= #{endTime} \n" +
            "\tAND ( a.out_source= 2 OR 2 = 2 ) \n" +
            "\tAND ( 1 = 1 ) \n" +
            "\tAND (\n" +
            "\t\tisnull( data0006.whouse_ptr, 0 ) = 0 \n" +
            "\t\tOR EXISTS ( SELECT 1 FROM get_factory_ptrTable ( 1 ) WHERE whse_rkey = isnull( data0048.make_ptr, data0006.whouse_ptr ) ) \n" +
            "\t) \n" +
            "\tAND DATA0034.barcode_entry_flag= 'Y' \n" +
            "GROUP BY\n" +
            "\tdata0034.seq_nr,\n" +
            "\tdata0034.dept_code,\n" +
            "\tdata0034.barcode_entry_flag,\n" +
            "\tdata0034.dept_name,\n" +
            "\tdata0034.yield_object_month,\n" +
            "\tDATA0034.yield_object \n" +
            "ORDER BY\n" +
            "\tdata0034.seq_nr,\n" +
            "\tdata0034.dept_code,\n" +
            "\tdata0034.barcode_entry_flag,\n" +
            "\tdata0034.dept_name")
    List<HistogramDto> capacityDetailsByDateType(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT\n" +
            "\tDATA0034.yield_object target,\n" +
            "\tDATA0034.yield_object_month temp,\n" +
            "\tround( SUM ( data0048.qty_prod* isnull( data0048.gs_sq_per_unit, data0047.Parameter_value ) ), 4 ) AS actual \n" +
            "FROM\n" +
            "\tdata0048 ( nolock )\n" +
            "\tINNER JOIN data0006 ( nolock ) ON data0048.wo_ptr= data0006.rkey\n" +
            "\tINNER JOIN data0025 ( nolock ) ON data0006.bom_ptr= data0025.rkey\n" +
            "\tINNER JOIN data0050 ( nolock ) ON data0025.ancestor_ptr= data0050.rkey\n" +
            "\tLEFT JOIN data0492 ( nolock ) ON data0006.cut_no= data0492.cut_no\n" +
            "\tLEFT JOIN data0010 ( nolock ) ON data0492.customer_ptr= data0010.rkey\n" +
            "\tLEFT JOIN data0047 ( nolock ) ON data0047.source_pointer= data0006.bom_ptr \n" +
            "\tAND data0047.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0047.PARAMETER_PTR= 4\n" +
            "\tLEFT JOIN data0047 data0047b ( nolock ) ON data0047b.source_pointer= data0006.bom_ptr \n" +
            "\tAND data0047b.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0047b.PARAMETER_PTR= 7\n" +
            "\tLEFT JOIN data0034 a ( nolock ) ON data0048.work_center_ptr= a.rkey\n" +
            "\tLEFT JOIN data0038 ( nolock ) ON data0038.source_ptr= data0025.rkey \n" +
            "\tAND data0038.FLOW_WHSE_PTR= data0006.WHOUSE_PTR \n" +
            "\tAND data0038.step_number= data0048.step_no \n" +
            "\tAND data0038.flow_no= data0048.flow_no\n" +
            "\tLEFT JOIN data0059 ( nolock ) ON data0050.rkey= data0059.cust_part_ptr \n" +
            "\tAND data0492.customer_ptr= data0059.customer_ptr\n" +
            "\tLEFT JOIN data0034 ( nolock ) ON a.dept_ptr= data0034.rkey \n" +
            "WHERE\n" +
            "\tdata0048.outtime >= #{startTime}\n" +
            "\tAND data0048.outtime <= #{endTime} \n" +
            "\tAND ( a.out_source= 2 OR 2 = 2 ) \n" +
            "\tAND ( 1 = 1 ) \n" +
            "\tAND (\n" +
            "\t\tisnull( data0006.whouse_ptr, 0 ) = 0 \n" +
            "\t\tOR EXISTS ( SELECT 1 FROM get_factory_ptrTable ( 1 ) WHERE whse_rkey = isnull( data0048.make_ptr, data0006.whouse_ptr ) ) \n" +
            "\t) \n" +
            "\tAND DATA0034.barcode_entry_flag= 'Y' \n" +
            "\tAND data0034.dept_name like concat('%', #{deptName}, '%')\n" +
            "GROUP BY\n" +
            "\tdata0034.seq_nr,\n" +
            "\tdata0034.dept_code,\n" +
            "\tdata0034.barcode_entry_flag,\n" +
            "\tdata0034.dept_name,\n" +
            "\tdata0034.yield_object_month,\n" +
            "\tDATA0034.yield_object \n" +
            "ORDER BY\n" +
            "\tdata0034.seq_nr,\n" +
            "\tdata0034.dept_code,\n" +
            "\tdata0034.barcode_entry_flag,\n" +
            "\tdata0034.dept_name")
    List<HistogramDto> capacityWorkshopDetailsByDateType(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);

    @Select("Select Data0060.Rkey As D60Rkey into #tmp_d60 From Data0060(nolock)\n" +
            "    left join Data0492(nolock) on Data0060.Sales_order=Data0492.So_no\n" +
            "    Where ISSUE_DATE>=Convert(DateTime, #{startTime}) and ISSUE_DATE<=Convert(DateTime, #{endTime})\n" +
            " If exists(Select 1 From tempdb..sysobjects Where id=object_id('tempdb..#tmpso'))  \n" +
            " drop table #tmpso \n" +
            " If exists(Select 1 From tempdb..sysobjects Where id=object_id('tempdb..#tmppsn'))\n" +
            " drop table #tmppsn\n" +
            " SELECT Data0492.SO_NO,Data0492.CUT_NO,Data0025.MANU_PART_NUMBER,data0097.PO_NUMBER,DATA0006.WORK_ORDER_NUMBER,\n" +
            "                  data0492.ISSUED_QTY as ISSUED_QTYpcs  ,(data0492.ISSUED_QTY / IsNull(Vhp0047.PARAMETER_VALUE,Data0047.PARAMETER_VALUE)) AS QTY_PLANNED  \n" +
            "\t\t\t\t  into #tmpso  From Data0058(nolock) Left Join\n" +
            "                          Data0158(nolock) on Data0058.mrb_no = Data0158.mrb_no left join\n" +
            "                          Data0006(nolock) On Data0058.WO_PTR=Data0006.RKey Left Join\n" +
            "                          Data0492(nolock) On Data0006.CUT_NO=Data0492.CUT_NO Left Join\n" +
            "                          Data0025(nolock) On Data0006.BOM_PTR=Data0025.RKey Left Join\n" +
            "                          Data0050(nolock) On Data0025.ANCESTOR_PTR=Data0050.RKey Left Join  Data0047(nolock) On Data0025.RKey=Data0047.SOURCE_POINTER and Data0047.FLOW_WHSE_PTR=data0006.WHOUSE_PTR  AND DATA0047.PARAMETER_PTR = 1 left join  Vhp0047(nolock)  on  dbo.Data0025.RKEY=Vhp0047.source_pointer and Vhp0047.so_ptr=Data0006.so_ptr AND Vhp0047.FLOW_WHSE_PTR=data0006.WHOUSE_PTR    and  Vhp0047.parameter_ptr=1  Left JOIN\n" +
            "                          Data0034(nolock) On Data0058.RESP_DEPT_PTR=Data0034.Rkey left join\n" +
            "                          Data0008(nolock) on Data0025.PROD_CODE_PTR=Data0008.Rkey left join\n" +
            "                          Data0039(nolock) on Data0058.REJECT_PTR=Data0039.Rkey left join\n" +
            "                          Data0059(nolock) on Data0050.Rkey=Data0059.cust_part_ptr\n" +
            "                            and Data0492.customer_ptr=Data0059.customer_ptr left join\n" +
            "                          Data0060(nolock) on Data0492.so_no=Data0060.sales_order left join\n" +
            "                          Data0010(nolock) on Data0060.customer_ptr=Data0010.Rkey\n" +
            "              left join data0097 on data0060.PURCHASE_ORDER_PTR=data0097.rkey\n" +
            "\n" +
            "                     where     Data0039.spec_type=0    and   (isnull(Data0006.WHOUSE_PTR,0)=0\n" +
            "                         or exists (select 1 from get_factory_ptrTable( 1 ) where Data0006.WHOUSE_PTR=whse_rkey )\n" +
            "                       ) and Exists(Select Top 1 * From #tmp_d60 Where D60Rkey=Data0006.So_ptr)  and DATA0058.QTY_REJECT/ IsNull(Vhp0047.PARAMETER_VALUE,Data0047.PARAMETER_VALUE)   >= 0            and Data0058.reject_ptr<>11850\n" +
            "select data0492.so_no, Data0025.MANU_PART_NUMBER\n" +
            ",sum(data0492.ISSUED_QTY) as ISSUED_QTYpcs\n" +
            ",Sum(data0492.Planned_qty) as Qty_planned  into #tmppsn  \n" +
            "from data0492(nolock)\n" +
            "left join data0060(nolock) on data0492.so_no=data0060.sales_order\n" +
            "left join data0025(nolock) on data0492.bom_ptr=data0025.rkey \n" +
            "Left Join  Data0047(nolock) On data0025.rkey=Data0047.SOURCE_POINTER and Data0047.FLOW_WHSE_PTR=data0492.whseptr  AND DATA0047.PARAMETER_PTR = 1 \n" +
            "left join  Vhp0047(nolock)  on  dbo.Data0492.bom_ptr=Vhp0047.source_pointer and Vhp0047.so_ptr=Data0060.rkey AND Vhp0047.FLOW_WHSE_PTR=data0492.WHSEPTR    and  Vhp0047.parameter_ptr=1 \n" +
            "Where Exists(Select Top 1 * From #tmp_d60 Where D60Rkey=Data0060.Rkey)  group by SO_NO,MANU_PART_NUMBER \n" +
            "\n" +
            "\n" +
            "SELECT data0492.SO_NO,\n" +
            "                    t1.ISSUED_QTYpcs 投产PCS数,data0034.DEPT_NAME, CONVERT(VARCHAR(10),dbo.data0058.TDATETIME,23)  TDATETIME,\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\n" +
            "               SUM(Data0058.QTY_REJECT/ IsNull(Vhp0047.PARAMETER_VALUE,Data0047.PARAMETER_VALUE)) AS 报废数量\n" +
            "INTO #tmp_d601\n" +
            "\t\t\t    \n" +
            "   From Data0058(nolock) left join\n" +
            "       Data0158(nolock) on Data0058.mrb_no = Data0158.mrb_no left join\n" +
            "       Data0006(nolock) on Data0058.WO_PTR=Data0006.Rkey left join\n" +
            "       Data0492(nolock) on Data0006.cut_no=Data0492.cut_no left join\n" +
            "       Data0060(nolock) on Data0492.so_no=Data0060.sales_order left join\n" +
            "       Data0025(nolock) on Data0006.bom_ptr=Data0025.Rkey\n" +
            "     left join data0097 on data0060.PURCHASE_ORDER_PTR=data0097.rkey left join\n" +
            "       Data0050(nolock) on Data0025.ancestor_ptr=Data0050.Rkey left join\n" +
            "       Data0059(nolock) on Data0050.Rkey=Data0059.cust_part_ptr\n" +
            "               and Data0060.customer_ptr=Data0059.customer_ptr left join\n" +
            "       Data0010(nolock) on Data0060.customer_ptr=Data0010.Rkey left join\n" +
            "       Data0008(nolock) on Data0025.PROD_CODE_PTR=Data0008.Rkey left join\n" +
            "       Data0039(nolock) on Data0058.REJECT_PTR=Data0039.Rkey left join\n" +
            "       Data0034(nolock) on Data0058.RESP_DEPT_PTR=Data0034.Rkey left join\n" +
            "       #tmppsn t1 On Data0492.SO_NO=t1.SO_NO and t1.MANU_PART_NUMBER =Data0025.MANU_PART_NUMBER left join\n" +
            "       Data0015(nolock) on Data0006.whouse_ptr=Data0015.Rkey left join  Data0047(nolock) On Data0025.RKey=Data0047.SOURCE_POINTER and Data0047.FLOW_WHSE_PTR=data0006.WHOUSE_PTR \n" +
            "\t   AND DATA0047.PARAMETER_PTR = 1 left join  Vhp0047(nolock) \n" +
            "\t   on  dbo.Data0025.RKEY=Vhp0047.source_pointer and Vhp0047.so_ptr=Data0006.so_ptr AND Vhp0047.FLOW_WHSE_PTR=data0006.WHOUSE_PTR    and  Vhp0047.parameter_ptr=1 \n" +
            "\t   where  Data0039.spec_type=0  \n" +
            "\t   and Exists(Select Top 1 * From #tmp_d60 Where D60Rkey=Data0006.So_ptr)  and  DATA0058.QTY_REJECT / IsNull(Vhp0047.PARAMETER_VALUE,Data0047.PARAMETER_VALUE)  >= 0       \n" +
            "\t   and Data0058.reject_ptr<>11850 Group by Data0010.Cust_code, Data0050.ttype,Data0008.EST_SCRAP_LTM,Data0008.EST_SCRAP1_LMT,po_number,DATA0006.WORK_ORDER_NUMBER,\n" +
            "                  Data0050.CUSTOMER_PART_NUMBER,Data0050.CP_REV,Data0050.Layers,Data0025.MANU_PART_NUMBER,\n" +
            "                  Data0059.CUSTOMER_PART_DESC,data0059.FIN_CUSTOMER_NAME,data0039.REJECT_DESCRIPTION,Data0034.DEPT_NAME,\n" +
            "                  Data0008.PRODUCT_NAME,data0492.SO_NO,data0492.SCH_COMPL_DATE,\n" +
            "                  t1.ISSUED_QTYpcs,t1.QTY_PLANNED,data0060.parts_ordered,data0006.whouse_ptr,\n" +
            "                  Vhp0047.PARAMETER_VALUE,Data0047.PARAMETER_VALUE,Data0015.abbr_name,Data0050.eng_tp,\n" +
            "\t\t\t\t  (case Data0050.ttype when 0 then Data0008.EST_SCRAP_LTM when 1 then Data0008.EST_SCRAP1_LMT end) , CONVERT(VARCHAR(10),dbo.data0058.TDATETIME,23)\n" +
            "\n" +
            "\n" +
            "\t\t\t\t  order by data0492.SO_NO \n" +
            "\n" +
            "\t\t\t\t  SELECT TDATETIME as temp,  DEPT_NAME as xAxis, ROUND(sum(报废数量)/SUM(投产PCS数)*100,2) as target,100-ROUND(sum(报废数量)/SUM(投产PCS数)*100,2) as actual FROM #tmp_d601 \n" +
            "\t\t\t\t   \n" +
            "\t\t\t\t  group by DEPT_NAME,TDATETIME\n" +
            "\t\t\t\t  order by TDATETIME\n" +
            "\t\t\t\t  DROP TABLE #tmp_d60\n" +
            "\t\t\t\t  DROP TABLE #tmp_d601")
    List<HistogramDto> getPassRateAndScrapRate(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);

    @Select("SELECT CONVERT(VARCHAR(10),DATA0101.LOG_DATE,23) as temp, COUNT(*) as actual FROM  Data0101  LEFT JOIN Data0118 ON  DATA0118.SOURCE_PTR=DATA0101.RKEY \n" +
            "LEFT JOIN DATA0039 ON DATA0039.RKEY=DATA0118.defect_ptr\n" +
            "LEFT JOIN DATA0034 ON DATA0034.RKEY=DATA0118.resp_dept_ptr\n" +
            " where DATA0101.LOG_DATE>= #{startTime} AND DATA0101.LOG_DATE<= #{endTime}  \n" +
            "and  DATA0034.DEPT_NAME  like concat('%', #{deptName}, '%')\n" +
            " group by DATA0034.DEPT_NAME , CONVERT(VARCHAR(10),DATA0101.LOG_DATE,23)")
    List<HistogramDto> getComplaintRate(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);

    @Select("select * into #temp from (\n" +
            "SELECT (select PARAMETER_VALUE from data0279 inner join data0278 on data0279.PARAMETER_PTR=data0278.rkey where source_ptr=data0025.rkey and SPEC_RKEY='5' and data0279.FLOW_WHSE_PTR=data0492.whseptr ) as lyl, isnull(isnull(data0050a.UNIT_SQ,isnull(data0050.UNIT_SQ,data0050b.UNIT_SQ)),data0050c.UNIT_SQ) * isnull(data0060.PARTS_ORDERED,data0060c.PARTS_ORDERED) as Order_sq , data0207.projects, isnull(isnull(data0050a.LAYERS,isnull(data0050.LAYERS,data0050b.LAYERS)),data0050c.LAYERS) as LAYERS,  data0008a.ANALYSIS_CODE3 as ANALYSIS_CODE3_08 ,data0008a.ANALYSIS_CODE4 as ANALYSIS_CODE4_08,data0008a.ANALYSIS_CODE5 as ANALYSIS_CODE5_08,Data0457.REF_NUMBER,Data0457.GON_NUMBER, data0456.grn_number, data0022.tax_2,data0456.create_date, data0457.Remark as Remark457,\n" +
            "isnull(isnull(data0050a.CP_REV,isnull(data0050.CP_REV,data0050b.CP_REV)),data0050c.CP_REV) as CP_REV ,data0103.GL_DESCRIPTION,data0103.GL_ACC_NUMBER,\n" +
            "isnull(isnull(data0050a.customer_part_number,isnull(data0050.customer_part_number,data0050b.customer_part_number)),data0050c.customer_part_number) as customer_part_number,\n" +
            "isnull(data0008.product_name,data0008a.product_name) as product_name,\n" +
            "      Data0017.INV_PART_NUMBER,Data0017.INV_NAME,\n" +
            "\t  data0017.inv_description+isnull(data0050.customer_part_number,isnull(data0050b.customer_part_number,''))+isnull(data0050.cp_rev,isnull(data0050b.cp_rev,'')) as inv_description,\n" +
            "\t  data0017.STOCK_BASE,  ROUND(Data0053.QUANTITY * ISNULL(Data0053.unit_area,DATA0050.UNIT_SQ), 4) AS so_mianqi,\n" +
            "      data0034.dept_code,Data0034.DEPT_NAME,Data0207.TDATE,Data0017.ANALYSIS_CODE1,Data0017.ANALYSIS_CODE2,\n" +
            "      Data0496.GROUP_NAME,data0496.group_desc,data0019.inv_group_name, data0019.inv_group_desc,data0019a.inv_group_name as inv_group_name1,data0019a.inv_group_desc as inv_group_desc1\n" +
            ",data0071.supp_part_no as MANUFACTURER_CODE,\n" +
            "      Data0015.WAREHOUSE_CODE, data0015.abbr_name as whse_name,Data0017.ANALYSIS_CODE4,Data0017.ANALYSIS_CODE5,\n" +
            "      Data0016.CODE AS code16, data0016.location,case Data0017.use_flag when '0' then '共用'  when '1' then '专用' end as use_flag,\n" +
            "      case data0496.ttype when 'P' then '直接材料' when 'M' then '间接材料' when 'N' then '其他材料' when 'T' then '外发项目'\n" +
            "\t                      when 'F' then '不计成本' when 'X' then '资产成本'  end grouptypedesc,\n" +
            "      Data0207.QUANTITY, Data0207.RTN_QUANTITY,DATA0022.quan_on_hand,\n" +
            "\t\n" +
            "\t   isnull(data0005_1.employee_name, data0005_2.employee_name) as employee_name1,\n" +
            "\t  -- isnull(data0005.employee_name,data0005_1.employee_name) as employee_name1,\n" +
            "\t  isnull(data0073.USER_FULL_NAME ,data0005.employee_name)as employee_name2,\n" +
            "      Data0001.CURR_CODE, data0070.po_number,data0078.auth_date,data0022.prod_date,data0022.EXPIRE_DATE,\n" +
            "      \n" +
            "      ROUND(Data0022.PRICE / Data0022.EXCHANGE_RATE, 6) AS STANDARD_price,\n" +
            "      \n" +
            "      Data0468.CUT_NO, Data0468.SO_NO, Data0022.SUPPLIER2,dbo.getvoucher(data0022.grn_ptr) as voucher,\n" +
            "     \n" +
            "\n" +
            "\t      area=convert(decimal(20,4),data0207.quantity*data0017.mat_len*mat_wth/1000000),\n" +
            "      Data0022.BARCODE_ID,data0022.Ref_BarCodeID, ROUND(Data0022.PRICE,6) as price,\n" +
            "      round(1/data0022.exchange_rate,8) as exchange_rate,ROUND(Data0022.PRICE * (1+data0022.tax_2/100),6) as TAX_PRICE,\n" +
            "      \n" +
            "      ROUND(Data0207.QUANTITY * Data0022.PRICE / Data0022.EXCHANGE_RATE, 6) AS total_price,\n" +
            "\t\tROUND(Data0207.QUANTITY * Data0022.PRICE / Data0022.EXCHANGE_RATE, 2) AS total_price2,\n" +
            "      ROUND(Data0207.QUANTITY * Data0022.PRICE * (1+data0022.tax_2/100) / Data0022.EXCHANGE_RATE,2) AS tax_amount2,\n" +
            "      ROUND(Data0207.QUANTITY * Data0022.PRICE * (1+data0022.tax_2/100) / Data0022.EXCHANGE_RATE,6) AS tax_amount,data0022.extra_req,\n" +
            "       Data0023.code,Data0023.ABBR_NAME, Data0023.SUPPLIER_NAME, Data0002.UNIT_NAME,DATA0022.Remark,d457Type=case Data0457.TType when 1 then '按照配料单'\n" +
            "      when 2 then  '直接到工序/总仓发料到车间'  when 3 then (case when data0207.invoice_ptr>0 then '发票反写记录' else '' end) when 4 then '车间向总仓退料' when 5 then '盘点出'\n" +
            "      when 6 then '转仓出' when 8 then '车间向装配线发料' when 9 then '装配线向车间退料' when 10 then  case isnull(data0207.so_ptr,0) when 0  then '车间/装配线特殊出' else '材料销售' end   else '' end,\n" +
            "      Data0457.BAN_NO,data0207.remark_info,Data0022.Reference_Number,Type96=Data0457.ttype,\n" +
            "      case when isnull(Data0207.QUANTITY,0)>0 then data0700.NOTE else data0703.Note end as NOTE,DATA0700.TDATE as T_DATE,DATA0700.SK_DATE as SK_DATE,\n" +
            "      data0060.SALES_ORDER,data0097.po_number as so_po_number\n" +
            "\t  ,data0700.tdate SLDATE,DATA0701.custom_item_1,DATA0701.custom_item_2,DATA0701.custom_item_3 ,  case isnull(data0492.ttype,0) when '0' then '正常投产 ' when '1' then '手工补料 '\n" +
            "\t  when '2' then '退货补料 ' when '3' then '正常返工 '\n" +
            "\t  when '4' then '退货返工 ' end  as ttype492, data0022.wo_datecode, data0068.remark as d68remark, Data0070.fob,\n" +
            "\t   dcust10.abbr_name as cust_abbr_name \n" +
            "\n" +
            " FROM Data0457(nolock)  \n" +
            "      inner join  Data0207(nolock) on Data0457.Rkey=Data0207.GON_PTR\n" +
            "      inner join Data0022(nolock) on Data0207.D0022_PTR = Data0022.RKEY\n" +
            "      \n" +
            "      \n" +
            "      left join Data0468(nolock) on Data0207.D0468_PTR = Data0468.RKEY\n" +
            "\t   left join data0492(nolock) on data0468.cut_no=Data0492.cut_no\n" +
            "      left join data0025(nolock) on data0492.bom_ptr=data0025.rkey\n" +
            "      left join data0050 as data0050a(nolock)  on data0025.ANCESTOR_PTR=data0050a.rkey\n" +
            "\t  left join data0010 as dcust10(nolock) on data0050a.CUSTOMER_PTR1=dcust10.rkey\n" +
            "      left join data0008(nolock) on data0025.PROD_CODE_PTR=data0008.rkey\n" +
            "     \n" +
            "\t   --left join Data0468 on Data0207.D0468_PTR = Data0468.RKEY\n" +
            "      left join data0492 as data0492a(nolock) on data0468.CUT_NO = data0492a.CUT_NO \n" +
            "      left join data0025 as data0025a(nolock) on data0492a.bom_ptr=data0025a.rkey\n" +
            "      left join data0050 as data0050c(nolock)  on data0025a.ANCESTOR_PTR=data0050c.rkey\n" +
            "      left join data0008 as data0008a(nolock) on data0025a.PROD_CODE_PTR=data0008a.rkey   \n" +
            "      \n" +
            "      Left  join  data0005 as data0005_1(nolock)  on data0005_1.rkey=data0457.Create_by\n" +
            "\t -- Left  join  data0073  on data0073.rkey=data0457.Create_by\n" +
            "\t  Left  join data0005(nolock) on Data0457.RECD_BY= data0005.rkey\n" +
            "\t  Left  join data0005 as data0005_2(nolock) on Data0457.AUDITED_BY= data0005_2.rkey\n" +
            "     left join Data0017(nolock) on Data0207.INVENTORY_PTR = Data0017.RKEY\n" +
            "     left join Data0002(nolock) on Data0017.STOCK_UNIT_PTR = Data0002.RKEY\n" +
            "     left join Data0015(nolock) on Data0022.whouse_ptr = Data0015.RKEY\n" +
            "     left join Data0016(nolock) on Data0022.LOCATION_PTR = Data0016.RKEY\n" +
            "     left join Data0496(nolock) on Data0017.GROUP_PTR = Data0496.RKEY\n" +
            "     left join Data0019(nolock) on Data0496.GROUP_PTR = Data0019.rkey\n" +
            "\t \t left join data0019 data0019a(nolock) on data0019.parent_ptr=data0019a.rkey\n" +
            "     left join Data0034(nolock) on Data0207.DEPT_PTR = Data0034.RKEY\n" +
            "     left join Data0001(nolock) on Data0022.CURRENCY_PTR = Data0001.RKEY\n" +
            "     left join Data0023(nolock) on Data0022.SUPPLIER_PTR = Data0023.RKEY\n" +
            "     left join Data0071(nolock) on Data0022.source_ptr=Data0071.rkey\n" +
            "\t left join data0103(nolock) on data0034.gl_acct_ptr=data0103.RKEY\n" +
            "     left join Data0070(nolock) on Data0071.po_ptr=Data0070.rkey\n" +
            "     left join (select PO_ptr=A.rkey,Auth_date=(select top 1 auth_date\n" +
            "        from data0078 B(nolock) where a.rkey=b.po_ptr order by auth_date desc)\n" +
            "        from data0070 A(nolock) ) AS data0078 on data0070.rkey=data0078.po_ptr\n" +
            "     left join data0456(nolock) on data0022.grn_ptr=data0456.rkey\n" +
            "     left join data0060(nolock) on data0207.so_ptr=data0060.rkey\n" +
            "     left join data0097(nolock) on data0060.PURCHASE_ORDER_PTR=data0097.rkey\n" +
            "     LEFT OUTER JOIN data0010 on data0060.customer_ptr=data0010.rkey\n" +
            "     left join data0703(nolock) on Data0457.REF_NUMBER=Data0703.rkey  \n" +
            "      left join data0060 as data0060b(nolock) on data0071.so_ptr=data0060b.rkey\n" +
            "\t  left join data0060 as data0060c on Data0492.SO_NO=data0060c.sales_order\n" +
            "  left join data0050 on data0060b.cust_part_ptr=data0050.rkey \n" +
            "  left join data0050 as data0050b(nolock)  on Data0071.MANU_PART_PTR=data0050b.rkey \n" +
            "  left JOIN Data0053(nolock) ON Data0053.CUST_PART_PTR=Data0050.RKEY\n" +
            "  left join data0701(nolock) on data0207.src_ptr =data0701.rkey\n" +
            "   left join Data0700(nolock) on data0701.DATA0700_RKEY =Data0700.rkey\n" +
            "   left join data0073(nolock) on data0700.USER_PTR=data0073.rkey\n" +
            "   left join data0069(nolock) on data0071.srce_ptr=data0069.rkey\n" +
            "   left join data0068(nolock) on data0069.PURCH_REQ_PTR=data0068.Rkey\n" +
            "     where 1= 1 AND (Data0207.QUANTITY <> 0)  and data0496.ttype<>'3' and data0496.ttype<>'T' and \n" +
            "\t   Data0207.tdate>=#{startTime} and \n" +
            "\t     Data0207.tdate<=#{endTime}\n" +
            "\t\t and ((isnull(data0207.so_ptr,0)>0 and  0=1) or 0 =0 ) and ((data0070.PO_TYPE<>'T' and data0070.PO_TYPE<>'N' and  0=1) or 0 =0 )\n" +
            "\n" +
            "union all \n" +
            " select '' as lyl, '' as Order_sq , projects='', LAYERS='', ANALYSIS_CODE3='',ANALYSIS_CODE4='',ANALYSIS_CODE5='', REF_NUMBER='',GON_NUMBER='',data0456.grn_number,data0022.tax_2,data0456.create_date,Remark457='',CP_REV='',\n" +
            " GL_DESCRIPTION='',GL_ACC_NUMBER='',\n" +
            " customer_part_number='',product_name='',\n" +
            " Data0017.INV_PART_NUMBER,Data0017.INV_NAME,\n" +
            " Data0017.INV_DESCRIPTION,Data0017.STOCK_BASE,null,\n" +
            "        dept_code='',DEPT_NAME='IQC',TDATE=Data0095.tran_date, \n" +
            "      Data0496.GROUP_NAME,data0496.group_desc,data0019.inv_group_name, data0019.inv_group_desc,data0019a.inv_group_name as inv_group_name1,data0019a.inv_group_desc as inv_group_desc1\n" +
            ",data0017.MANUFACTURER_CODE, \n" +
            "      Data0015.WAREHOUSE_CODE, data0015.abbr_name as whse_name,\n" +
            "      Data0016.CODE AS code16, data0016.location,case Data0017.use_flag when '0' then '共用'  when '1' then '专用' end as use_flag, \n" +
            "      case data0496.ttype when 'P' then '直接材料' when 'M' then '间接材料' when 'N' then '其他材料' when 'T' then '外发项目' when 'F' then '不计成本' when 'X' then '资产成本' end grouptypedesc,\n" +
            "\t  Data0017.ANALYSIS_CODE1, \n" +
            "     Data0017.ANALYSIS_CODE2,Data0017.ANALYSIS_CODE4,Data0017.ANALYSIS_CODE5, QUANTITY=sum(Data0095.QUANTITY),RTN_QUANTITY=0, quan_on_hand=0,\n" +
            "        data0005.employee_name AS employee_name1,I.employee_name AS employee_name2,Data0001.CURR_CODE,po_number='',auth_date=Data0095.tran_date, \n" +
            "       data0022.prod_date,data0022.EXPIRE_DATE,ROUND(Data0022.PRICE / Data0022.EXCHANGE_RATE, 4) AS STANDARD_COST, \n" +
            "        CUT_NO='',SO_NO='',Data0022.SUPPLIER2,'',area=sum(convert(decimal(20,4),data0095.quantity*data0017.mat_len*mat_wth)),Data0022.BARCODE_ID,data0022.Ref_BarCodeID,ROUND(Data0022.PRICE,4) as price, \n" +
            "       round(1/data0022.exchange_rate,8) as exchange_rate,ROUND(Data0022.PRICE * (1+data0022.tax_2/100),6) as TAX_PRICE, \n" +
            "        sum(ROUND(Data0095.QUANTITY * Data0022.PRICE / Data0022.EXCHANGE_RATE,6)) AS total_price, \n" +
            "\t\tsum(ROUND(Data0095.QUANTITY * Data0022.PRICE / Data0022.EXCHANGE_RATE, 2)) AS total_price2,\n" +
            "     sum( ROUND(Data0095.QUANTITY * Data0022.PRICE * (1+data0022.tax_2/100) / Data0022.EXCHANGE_RATE,2)) AS tax_amount2,\n" +
            "        sum(ROUND(Data0095.QUANTITY * Data0022.PRICE * (1+data0022.tax_2/100) / Data0022.EXCHANGE_RATE,6)) AS tax_amount,data0022.extra_req,\n" +
            "        Data0023.code,Data0023.ABBR_NAME,Data0023.SUPPLIER_NAME,Data0002.UNIT_NAME,'' as Remark,\n" +
            "        case when tran_tp=38 then '返回IQC退回' else 'IQC报废' end as d457Type,BAN_NO='',remark_info='',Reference_Number='',T_DATE='',SK_DATE='',Type96=Data0456.ttype,Note='',\n" +
            "        SALES_ORDER='',so_po_number=''  \n" +
            "  \t  ,NULL SLDATE,NULL custom_item_1,NULL custom_item_2,NULL custom_item_3  ,null, data0022.wo_datecode, data0068.remark as d68remark, data0070.fob, '' as cust_abbr_name\n" +
            " from data0095(nolock) \n" +
            "  inner join data0022(nolock) on Data0095.srce_ptr=data0022.rkey \n" +
            "  left join Data0071(nolock) on Data0022.SOURCE_PTR = Data0071.RKEY\n" +
            "  left join data0070(nolock) on data0071.PO_PTR=data0070.rkey\n" +
            "  left join Data0005(nolock) on Data0095.tran_by=Data0005.Rkey \n" +
            "  left join Data0005  I(nolock) on Data0095.REC_BY=I.Rkey \n" +
            "  inner join data0017(nolock)   on   data0022.inventory_ptr=data0017.rkey \n" +
            "  inner join data0023(nolock)   on   data0022.supplier_ptr=data0023.rkey \n" +
            "  left join  data0496(nolock)  on   data0017.group_ptr=data0496.rkey \n" +
            "  left join Data0019(nolock) on Data0496.GROUP_PTR = Data0019.rkey \n" +
            "  left join data0019 data0019a(nolock) on data0019.parent_ptr=data0019a.rkey\n" +
            "  inner join data0002(nolock)   on   data0017.stock_unit_ptr=data0002.rkey \n" +
            "  left join Data0001(nolock) on Data0022.CURRENCY_PTR = Data0001.RKEY \n" +
            "  left join Data0015(nolock) on Data0022.whouse_ptr = Data0015.RKEY \n" +
            "  left join Data0016(nolock) on Data0022.LOCATION_PTR = Data0016.RKEY \n" +
            "  left join Data0456(nolock) on Data0022.grn_ptr = Data0456.RKEY \n" +
            "  left join data0034 on 1=2\n" +
            "  left join data0069(nolock) on data0071.srce_ptr=data0069.rkey\n" +
            "  left join data0068(nolock) on data0069.PURCH_REQ_PTR=data0068.Rkey\n" +
            "  where 1= 1 and data0496.ttype<>'3' and data0496.ttype<>'T' and Data0095.tran_tp in (38,39)  \n" +
            "   and (( data0095.RKEY=0 and  0=1) or 0 =0 ) and   data0095.tran_date>=#{startTime}\n" +
            "   and   data0095.tran_date<= #{endTime}\n" +
            " group by data0022.tax_2,Data0017.INV_PART_NUMBER,Data0017.INV_NAME,Data0017.INV_DESCRIPTION,Data0017.STOCK_BASE,Data0017.use_flag,Data0017.ANALYSIS_CODE1,Data0017.ANALYSIS_CODE2, \n" +
            "        Data0496.GROUP_name,data0496.group_desc,Data0095.tran_date,data0005.employee_name,I.employee_name,Data0001.CURR_CODE,Data0017.ANALYSIS_CODE4,Data0017.ANALYSIS_CODE5,data0017.MANUFACTURER_CODE, \n" +
            "        Data0095.tran_date,data0022.prod_date,data0022.EXPIRE_DATE,data0015.warehouse_code,data0015.abbr_name,Data0016.CODE,data0016.location,Data0022.PRICE,Data0022.EXCHANGE_RATE,data0022.extra_req, \n" +
            "        Data0022.SUPPLIER2,DATA0022.Remark,Data0022.BARCODE_ID,data0019.inv_group_name,data0019.inv_group_desc,data0496.ttype, data0022.Ref_BarCodeID,data0456.create_date,\n" +
            "        Data0023.code,Data0023.ABBR_NAME,Data0023.SUPPLIER_NAME,Data0002.UNIT_NAME,data0017.mat_len,data0017.mat_wth,data0456.grn_number,Data0456.ttype,data0095.tran_tp,data0019.inv_group_name,\n" +
            "\t\tdata0019a.inv_group_name,data0019.inv_group_desc,data0019a.inv_group_desc,data0022.wo_datecode,data0068.remark, data0070.fob\n" +
            "\n" +
            "  ) a\n" +
            "  SELECT DEPT_NAME deptName,  ROUND(SUM(tax_amount),2) amount   FROM #temp\n" +
            " where DEPT_NAME like concat('%', #{deptName}, '%')\n" +
            " group by DEPT_NAME\n" +
            "\n" +
            "  DROP TABLE #temp")
    List<CostDto> getMaterialCost(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);

    @Select("SELECT DATA0105.VOUCHER,\n" +
            "   CONVERT(VARCHAR(10),dbo.DATA0105.ENTERED_DT,23) ENTERED_DT1,DATA0105.FYEAR,DATA0105.PERIOD,\n" +
            "   A.EMPLOYEE_NAME AS OPERATOR,DATA0106.GL_ACCT_NO_PTR,DATA0001.CURR_CODE,DATA0106.ORIG_AMOUNT,\n" +
            "B.EMPLOYEE_NAME AS AUDITOR,C.USER_FULL_NAME  AS POSTMAN,DATA0106.DESCRIPTION,DATA0106.D_C,\n" +
            "DATA0106.AMOUNT,DATA0103.GL_ACC_NUMBER,DATA0103.GL_DESCRIPTION,\n" +
            "\n" +
            " RTRIM(data0103.GL_ACC_NUMBER)+'-'+LTRIM(data0103.GL_DESCRIPTION) km,RTRIM(\n" +
            " CASE  WHEN (NOT DATA0010.CUST_CODE IS NULL)\n" +
            "      THEN '['+DATA0010.CUST_CODE+']'+LTRIM(DATA0010.CUSTOMER_NAME)\n" +
            "      WHEN (NOT DATA0023.CODE IS NULL)\n" +
            "      THEN '['+DATA0023.CODE+']'+LTRIM(DATA0023.SUPPLIER_NAME)\n" +
            "      ELSE  ''\n" +
            " END  )AS REMARK,\n" +
            " CASE  WHEN (NOT DATA0034.DEPT_NAME IS NULL)\n" +
            "      THEN DATA0034.DEPT_NAME\n" +
            "      WHEN (NOT DATA0005.EMPLOYEE_NAME IS NULL)\n" +
            "      THEN data0005.EMPLOYEE_NAME\n" +
            "\t  WHEN (NOT DATA0083.ITEM_NAME IS NULL) then DATA0083.ITEM_NAME\n" +
            "      ELSE  ''\n" +
            " END  AS REMARK1,case when data0106.D_C='D' THEN AMT  END  AS amount_d,\n" +
            " case when data0106.D_C='C' THEN AMT  END  AS amount_C,\n" +
            "1 / NULLIF(DATA0106.EXCH_RATE,0) AS  EXCH_RATE ,DATA0106.QUANTITY,DATA0106.PRICE,DATA0103.UNIT\n" +
            ",DATA0034.DEPT_NAME,DATA0005.EMPLOYEE_NAME,DATA0010.ABBR_NAME CUSTOMER_NAME,DATA0023.ABBR_NAME SUPPLIER_NAME,DATA0083.ITEM_NAME\n" +
            "\n" +
            " INTO #TEMP\n" +
            "from data0106(nolock)\n" +
            "inner join data0105(nolock) on data0106.gl_header_ptr=data0105.rkey\n" +
            "inner join data0001(nolock) on data0106.curr_ptr=data0001.rkey\n" +
            "inner join Data0103(nolock) on Data0106.gl_acct_no_ptr=data0103.rkey\n" +
            "left join data0005 a(nolock) on data0105.entered_by_empl_ptr=a.rkey\n" +
            "left join data0005 b(nolock) on data0105.audited_by_empl_ptr=b.rkey\n" +
            "left join data0073 c (nolock) on data0105.posted_by_empl_ptr=c.rkey\n" +
            "left join data0010(nolock) on ISNULL(data0105.customer_ptr,DATA0106.CUST_PTR)=data0010.rkey\n" +
            "left join data0023(nolock) on ISNULL(data0105.supp_ptr,DATA0106.SUP_PTR)=data0023.rkey \n" +
            "LEFT JOIN DATA0034(NOLOCK) ON DATA0106.DEPT_PTR=DATA0034.RKEY\n" +
            "LEFT JOIN DATA0005(NOLOCK) ON DATA0106.EMPL_PTR=DATA0005.RKEY\n" +
            "LEFT JOIN DATA0083_2 AS DATA0083(NOLOCK) ON DATA0106.D083_PTR=DATA0083.RKEY\n" +
            " WHERE DATA0103.GL_ACC_NUMBER='5101004' --工资科目\n" +
            " AND DATA0105.ENTERED_DT>=#{startTime} AND DATA0105.ENTERED_DT<=#{endTime}\n" +
            " \n" +
            " order by data0105.voucher, -ascii(data0106.d_c) \n" +
            "   SELECT VOUCHER,SUM(amount_d) Samount_d,SUM(amount_C) Samount_C,DBO.fn_ChnMoney(sum(amount_d)) 大小写 into #temp1\n" +
            "   \n" +
            "   FROM #TEMP\n" +
            "   GROUP BY VOUCHER \n" +
            "   select  FYEAR year,PERIOD month,DEPT_NAME deptName,\n" +
            "   sum(amount_d )amount    from  #TEMP left join #TEMP1 on #TEMP.VOUCHER=#TEMP1.VOUCHER\n" +
            "   where DEPT_NAME like concat('%', #{deptName}, '%')\n" +
            "   group by FYEAR,PERIOD,DEPT_NAME\n" +
            "   DROP TABLE #TEMP\n" +
            "    DROP TABLE #TEMP1")
    List<CostDto> getManpowerCost(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);
    @Select("SELECT DATA0105.VOUCHER,\n" +
            "   \n" +
            "   CONVERT(VARCHAR(10),dbo.DATA0105.ENTERED_DT,23) ENTERED_DT1,DATA0105.FYEAR,DATA0105.PERIOD,\n" +
            "   A.EMPLOYEE_NAME AS OPERATOR,DATA0106.GL_ACCT_NO_PTR,DATA0001.CURR_CODE,DATA0106.ORIG_AMOUNT,\n" +
            "B.EMPLOYEE_NAME AS AUDITOR,C.USER_FULL_NAME  AS POSTMAN,DATA0106.DESCRIPTION,DATA0106.D_C,\n" +
            "DATA0106.AMOUNT,DATA0103.GL_ACC_NUMBER,DATA0103.GL_DESCRIPTION,\n" +
            "\n" +
            " RTRIM(data0103.GL_ACC_NUMBER)+'-'+LTRIM(data0103.GL_DESCRIPTION) km,RTRIM(\n" +
            " CASE  WHEN (NOT DATA0010.CUST_CODE IS NULL)\n" +
            "      THEN '['+DATA0010.CUST_CODE+']'+LTRIM(DATA0010.CUSTOMER_NAME)\n" +
            "      WHEN (NOT DATA0023.CODE IS NULL)\n" +
            "      THEN '['+DATA0023.CODE+']'+LTRIM(DATA0023.SUPPLIER_NAME)\n" +
            "      ELSE  ''\n" +
            " END  )AS REMARK,\n" +
            " CASE  WHEN (NOT DATA0034.DEPT_NAME IS NULL)\n" +
            "      THEN DATA0034.DEPT_NAME\n" +
            "      WHEN (NOT DATA0005.EMPLOYEE_NAME IS NULL)\n" +
            "      THEN data0005.EMPLOYEE_NAME\n" +
            "\t  WHEN (NOT DATA0083.ITEM_NAME IS NULL) then DATA0083.ITEM_NAME\n" +
            "      ELSE  ''\n" +
            " END  AS REMARK1,case when data0106.D_C='D' THEN AMT  END  AS amount_d,\n" +
            " case when data0106.D_C='C' THEN AMT  END  AS amount_C,\n" +
            "1 / NULLIF(DATA0106.EXCH_RATE,0) AS  EXCH_RATE ,DATA0106.QUANTITY,DATA0106.PRICE,DATA0103.UNIT\n" +
            ",DATA0034.DEPT_NAME,DATA0005.EMPLOYEE_NAME,DATA0010.ABBR_NAME CUSTOMER_NAME,DATA0023.ABBR_NAME SUPPLIER_NAME,DATA0083.ITEM_NAME\n" +
            "\n" +
            " INTO #TEMP\n" +
            "from data0106(nolock)\n" +
            "inner join data0105(nolock) on data0106.gl_header_ptr=data0105.rkey\n" +
            "inner join data0001(nolock) on data0106.curr_ptr=data0001.rkey\n" +
            "inner join Data0103(nolock) on Data0106.gl_acct_no_ptr=data0103.rkey\n" +
            "left join data0005 a(nolock) on data0105.entered_by_empl_ptr=a.rkey\n" +
            "left join data0005 b(nolock) on data0105.audited_by_empl_ptr=b.rkey\n" +
            "left join data0073 c (nolock) on data0105.posted_by_empl_ptr=c.rkey\n" +
            "left join data0010(nolock) on ISNULL(data0105.customer_ptr,DATA0106.CUST_PTR)=data0010.rkey\n" +
            "left join data0023(nolock) on ISNULL(data0105.supp_ptr,DATA0106.SUP_PTR)=data0023.rkey \n" +
            "LEFT JOIN DATA0034(NOLOCK) ON DATA0106.DEPT_PTR=DATA0034.RKEY\n" +
            "LEFT JOIN DATA0005(NOLOCK) ON DATA0106.EMPL_PTR=DATA0005.RKEY\n" +
            "LEFT JOIN DATA0083_2 AS DATA0083(NOLOCK) ON DATA0106.D083_PTR=DATA0083.RKEY\n" +
            " WHERE DATA0103.GL_ACC_NUMBER in ('5101002') --电科目\n" +
            " AND DATA0105.ENTERED_DT>=#{startTime} AND DATA0105.ENTERED_DT<=#{endTime}\n" +
            " \n" +
            " order by data0105.voucher, -ascii(data0106.d_c) \n" +
            "   SELECT VOUCHER,SUM(amount_d) Samount_d,SUM(amount_C) Samount_C,DBO.fn_ChnMoney(sum(amount_d)) 大小写 into #temp1\n" +
            "   \n" +
            "   FROM #TEMP\n" +
            "   GROUP BY VOUCHER \n" +
            "   select  FYEAR year,PERIOD month,DEPT_NAME deptName,\n" +
            "   \n" +
            "   sum(amount_d) amount    from  #TEMP left join #TEMP1 on #TEMP.VOUCHER=#TEMP1.VOUCHER\n" +
            "   where DEPT_NAME like concat('%', #{deptName}, '%')\n" +
            "   group by FYEAR,PERIOD,DEPT_NAME\n" +
            "   DROP TABLE #TEMP\n" +
            "    DROP TABLE #TEMP1")
    List<CostDto> getElectricityCost(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);
    @Select("SELECT DATA0105.VOUCHER,\n" +
            "   \n" +
            "   CONVERT(VARCHAR(10),dbo.DATA0105.ENTERED_DT,23) ENTERED_DT1,DATA0105.FYEAR,DATA0105.PERIOD,\n" +
            "   \n" +
            "   \n" +
            "   A.EMPLOYEE_NAME AS OPERATOR,DATA0106.GL_ACCT_NO_PTR,DATA0001.CURR_CODE,DATA0106.ORIG_AMOUNT,\n" +
            "B.EMPLOYEE_NAME AS AUDITOR,C.USER_FULL_NAME  AS POSTMAN,DATA0106.DESCRIPTION,DATA0106.D_C,\n" +
            "DATA0106.AMOUNT,DATA0103.GL_ACC_NUMBER,DATA0103.GL_DESCRIPTION,\n" +
            "\n" +
            " RTRIM(data0103.GL_ACC_NUMBER)+'-'+LTRIM(data0103.GL_DESCRIPTION) km,RTRIM(\n" +
            " CASE  WHEN (NOT DATA0010.CUST_CODE IS NULL)\n" +
            "      THEN '['+DATA0010.CUST_CODE+']'+LTRIM(DATA0010.CUSTOMER_NAME)\n" +
            "      WHEN (NOT DATA0023.CODE IS NULL)\n" +
            "      THEN '['+DATA0023.CODE+']'+LTRIM(DATA0023.SUPPLIER_NAME)\n" +
            "      ELSE  ''\n" +
            " END  )AS REMARK,\n" +
            " CASE  WHEN (NOT DATA0034.DEPT_NAME IS NULL)\n" +
            "      THEN DATA0034.DEPT_NAME\n" +
            "      WHEN (NOT DATA0005.EMPLOYEE_NAME IS NULL)\n" +
            "      THEN data0005.EMPLOYEE_NAME\n" +
            "\t  WHEN (NOT DATA0083.ITEM_NAME IS NULL) then DATA0083.ITEM_NAME\n" +
            "      ELSE  ''\n" +
            " END  AS REMARK1,case when data0106.D_C='D' THEN AMT  END  AS amount_d,\n" +
            " case when data0106.D_C='C' THEN AMT  END  AS amount_C,\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "1 / NULLIF(DATA0106.EXCH_RATE,0) AS  EXCH_RATE ,DATA0106.QUANTITY,DATA0106.PRICE,DATA0103.UNIT\n" +
            ",DATA0034.DEPT_NAME,DATA0005.EMPLOYEE_NAME,DATA0010.ABBR_NAME CUSTOMER_NAME,DATA0023.ABBR_NAME SUPPLIER_NAME,DATA0083.ITEM_NAME\n" +
            "\n" +
            " INTO #TEMP\n" +
            "from data0106(nolock)\n" +
            "inner join data0105(nolock) on data0106.gl_header_ptr=data0105.rkey\n" +
            "inner join data0001(nolock) on data0106.curr_ptr=data0001.rkey\n" +
            "inner join Data0103(nolock) on Data0106.gl_acct_no_ptr=data0103.rkey\n" +
            "left join data0005 a(nolock) on data0105.entered_by_empl_ptr=a.rkey\n" +
            "left join data0005 b(nolock) on data0105.audited_by_empl_ptr=b.rkey\n" +
            "left join data0073 c (nolock) on data0105.posted_by_empl_ptr=c.rkey\n" +
            "left join data0010(nolock) on ISNULL(data0105.customer_ptr,DATA0106.CUST_PTR)=data0010.rkey\n" +
            "left join data0023(nolock) on ISNULL(data0105.supp_ptr,DATA0106.SUP_PTR)=data0023.rkey \n" +
            "LEFT JOIN DATA0034(NOLOCK) ON DATA0106.DEPT_PTR=DATA0034.RKEY\n" +
            "LEFT JOIN DATA0005(NOLOCK) ON DATA0106.EMPL_PTR=DATA0005.RKEY\n" +
            "LEFT JOIN DATA0083_2 AS DATA0083(NOLOCK) ON DATA0106.D083_PTR=DATA0083.RKEY\n" +
            " WHERE DATA0103.GL_ACC_NUMBER in ('5101001') --水科目\n" +
            " AND DATA0105.ENTERED_DT>=#{startTime} AND DATA0105.ENTERED_DT<=#{endTime}\n" +
            " \n" +
            " order by data0105.voucher, -ascii(data0106.d_c) \n" +
            "   SELECT VOUCHER,SUM(amount_d) Samount_d,SUM(amount_C) Samount_C,DBO.fn_ChnMoney(sum(amount_d)) 大小写 into #temp1\n" +
            "   \n" +
            "   FROM #TEMP\n" +
            "   GROUP BY VOUCHER \n" +
            "   select  FYEAR year,PERIOD month,DEPT_NAME deptName,\n" +
            "   \n" +
            "   sum(amount_d) amount    from  #TEMP left join #TEMP1 on #TEMP.VOUCHER=#TEMP1.VOUCHER\n" +
            "   where DEPT_NAME like concat('%', #{deptName}, '%')\n" +
            "   group by FYEAR,PERIOD,DEPT_NAME\n" +
            "   DROP TABLE #TEMP\n" +
            "    DROP TABLE #TEMP1")
    List<CostDto> getWaterCost(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);
    @Select("SELECT DATA0105.VOUCHER,\n" +
            "   CONVERT(VARCHAR(10),dbo.DATA0105.ENTERED_DT,23) ENTERED_DT1,DATA0105.FYEAR,DATA0105.PERIOD,\n" +
            "   A.EMPLOYEE_NAME AS OPERATOR,DATA0106.GL_ACCT_NO_PTR,DATA0001.CURR_CODE,DATA0106.ORIG_AMOUNT,\n" +
            "B.EMPLOYEE_NAME AS AUDITOR,C.USER_FULL_NAME  AS POSTMAN,DATA0106.DESCRIPTION,DATA0106.D_C,\n" +
            "DATA0106.AMOUNT,DATA0103.GL_ACC_NUMBER,DATA0103.GL_DESCRIPTION,\n" +
            "\n" +
            " RTRIM(data0103.GL_ACC_NUMBER)+'-'+LTRIM(data0103.GL_DESCRIPTION) km,RTRIM(\n" +
            " CASE  WHEN (NOT DATA0010.CUST_CODE IS NULL)\n" +
            "      THEN '['+DATA0010.CUST_CODE+']'+LTRIM(DATA0010.CUSTOMER_NAME)\n" +
            "      WHEN (NOT DATA0023.CODE IS NULL)\n" +
            "      THEN '['+DATA0023.CODE+']'+LTRIM(DATA0023.SUPPLIER_NAME)\n" +
            "      ELSE  ''\n" +
            " END  )AS REMARK,\n" +
            " CASE  WHEN (NOT DATA0034.DEPT_NAME IS NULL)\n" +
            "      THEN DATA0034.DEPT_NAME\n" +
            "      WHEN (NOT DATA0005.EMPLOYEE_NAME IS NULL)\n" +
            "      THEN data0005.EMPLOYEE_NAME\n" +
            "\t  WHEN (NOT DATA0083.ITEM_NAME IS NULL) then DATA0083.ITEM_NAME\n" +
            "      ELSE  ''\n" +
            " END  AS REMARK1,case when data0106.D_C='D' THEN AMT  END  AS amount_d,\n" +
            " case when data0106.D_C='C' THEN AMT  END  AS amount_C,\n" +
            "1 / NULLIF(DATA0106.EXCH_RATE,0) AS  EXCH_RATE ,DATA0106.QUANTITY,DATA0106.PRICE,DATA0103.UNIT\n" +
            ",DATA0034.DEPT_NAME,DATA0005.EMPLOYEE_NAME,DATA0010.ABBR_NAME CUSTOMER_NAME,DATA0023.ABBR_NAME SUPPLIER_NAME,DATA0083.ITEM_NAME\n" +
            "\n" +
            " INTO #TEMP\n" +
            "from data0106(nolock)\n" +
            "inner join data0105(nolock) on data0106.gl_header_ptr=data0105.rkey\n" +
            "inner join data0001(nolock) on data0106.curr_ptr=data0001.rkey\n" +
            "inner join Data0103(nolock) on Data0106.gl_acct_no_ptr=data0103.rkey\n" +
            "left join data0005 a(nolock) on data0105.entered_by_empl_ptr=a.rkey\n" +
            "left join data0005 b(nolock) on data0105.audited_by_empl_ptr=b.rkey\n" +
            "left join data0073 c (nolock) on data0105.posted_by_empl_ptr=c.rkey\n" +
            "left join data0010(nolock) on ISNULL(data0105.customer_ptr,DATA0106.CUST_PTR)=data0010.rkey\n" +
            "left join data0023(nolock) on ISNULL(data0105.supp_ptr,DATA0106.SUP_PTR)=data0023.rkey \n" +
            "LEFT JOIN DATA0034(NOLOCK) ON DATA0106.DEPT_PTR=DATA0034.RKEY\n" +
            "LEFT JOIN DATA0005(NOLOCK) ON DATA0106.EMPL_PTR=DATA0005.RKEY\n" +
            "LEFT JOIN DATA0083_2 AS DATA0083(NOLOCK) ON DATA0106.D083_PTR=DATA0083.RKEY\n" +
            " WHERE DATA0103.GL_ACC_NUMBER IN ('5101003001', '5101003002','5101003003','5101003004','5101007','5101008001','5101008002','5101008003','5101008004')--工资科目\n" +
            " AND DATA0105.ENTERED_DT>=#{startTime} AND DATA0105.ENTERED_DT<=#{endTime}\n" +
            " \n" +
            " order by data0105.voucher, -ascii(data0106.d_c) \n" +
            "   SELECT VOUCHER,SUM(amount_d) Samount_d,SUM(amount_C) Samount_C,DBO.fn_ChnMoney(sum(amount_d)) 大小写 into #temp1\n" +
            "   \n" +
            "   FROM #TEMP\n" +
            "   GROUP BY VOUCHER \n" +
            "   select  FYEAR year,PERIOD month,DEPT_NAME deptName,\n" +
            "   SUM(amount_d) amount    from  #TEMP left join #TEMP1 on #TEMP.VOUCHER=#TEMP1.VOUCHER\n" +
            "   where DEPT_NAME like concat('%', #{deptName}, '%')\n" +
            "   GROUP BY FYEAR,PERIOD,DEPT_NAME\n" +
            "   \n" +
            "   DROP TABLE #TEMP\n" +
            "    DROP TABLE #TEMP1")
    List<CostDto> getOtherCost(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("deptName") String deptName);
}
